﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using OuterParser.Models;

namespace OuterParser.Transporter
{
    public class StagingDbTransporter
    {
        private PulsatrixDbEntities pulsatrixDb { get; set; }
        private PulsatrixStagingDbEntities stagingDb { get; set; }
        public StagingDbTransporter()
        {
            this.pulsatrixDb = new PulsatrixDbEntities();
            this.stagingDb = new PulsatrixStagingDbEntities();
        }

        /// <summary>
        /// Launches all converting process.
        /// </summary>
        /// <returns>Null if all is OK. Exception if it was caught.</returns>
        public Exception Launch()
        {
            //   try
            {
                //ClearDatabase();
                //AddInitialValues();
                //TransportProfessionGroups();
                //TransportProfessions();
                TransportYears();
                TransportStates();
                TransportRegions();
                TransportCities();
                TransportUniversityTypes();
                TransportUniversities();
                TransportGallery();
                TransportFaculties();
                TransportEQGroups();
                TransportEQ();
                TransportBranches();
                TransportDirectionTypes();
                TransportDirections();
                TransportSubjects();
                TransportEducationForms();
                TransportSpecialities();
                TransportSpecializations();
                //TransportAnnualSets(); USE SCRIPT FOR THIS
                //TransportAnnualSetSubjects(); USE SCRIPT FOR THIS
                return null;
            }
            //catch (Exception exc) { return exc; }
        }
        private void AddInitialValues()
        {
            Console.Write("Adding initial values => ");
            pulsatrixDb.Year.Add(new Year()
            {
                Value = 0
            });
            pulsatrixDb.SaveChanges();
            pulsatrixDb.State.Add(new State()
            {
                Name = "Не визначено"
            });
            pulsatrixDb.SaveChanges();
            pulsatrixDb.Region.Add(new Region()
            {
                Name = "Не визначено",
                StateId = 0
            });
            pulsatrixDb.SaveChanges();
            pulsatrixDb.City.Add(new City()
            {
                Name = "Не визначено",
                StateId = 0
            });
            pulsatrixDb.SaveChanges();
            pulsatrixDb.UniversityType.Add(new UniversityType()
            {
                Value = "Не визначено"
            });
            pulsatrixDb.SaveChanges();
            pulsatrixDb.University.Add(new University()
            {
                Name = "Не визначено",
                Type = 0,
                State = 0,
                City = 0,
                Region = 0,
                YearId = 0
            });
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void ClearDatabase()
        {
            Console.Write("Cleaning the DB => ");
            try
            {
                string script = "";
                string path = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName + @"\MainDbCleaner.sql";
                if (File.Exists(path))
                {
                    using (FileStream fs = new FileStream(path, FileMode.Open))
                    {
                        using (StreamReader reader = new StreamReader(fs))
                        {
                            script = reader.ReadToEnd();
                            DbCommandExecuter executer = new DbCommandExecuter("PulsatrixDbEntities");
                            Console.Write(executer.ExecuteCommand(script).RecordsAffected + " records affected in common. ");
                        }
                    }
                    Console.Write(" OK\n");
                }
                else Console.WriteLine("Script MainDbCleaner.sql doesn't exist in current directory: " + path);
            }
            catch (Exception exc) { Console.WriteLine(exc.Message); }
        }
        private void TransportYears()
        {
            Console.Write("Years transportation => ");
            foreach (var item in stagingDb.VstupDb_Year)
            {
                if (item.YearId != 0)
                    pulsatrixDb.Year.Add(new Year()
                    {
                        ID = item.YearId,
                        Value = item.Value
                    });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportStates()
        {
            Console.Write("States transportation => ");
            foreach (var item in stagingDb.RealvnzDb_Region)
            {
                pulsatrixDb.State.Add(new State()
                {
                    Name = RemoveSpaces(item.Name)
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportRegions()
        {
            Console.Write("Regions transportation => ");
            foreach (var item in stagingDb.RealvnzDb_Department)
            {
                string temp = RemoveSpaces(item.RealvnzDb_Region.Name);
                pulsatrixDb.Region.Add(new Region()
                {
                    Name = RemoveSpaces(item.Name),
                    StateId = pulsatrixDb.State.FirstOrDefault(x => x.Name == temp).ID
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportCities()
        {
            Console.Write("Cities transportation => ");
            foreach (var item in stagingDb.RealvnzDb_City)
            {
                string temp = RemoveSpaces(item.RealvnzDb_Region.Name);
                pulsatrixDb.City.Add(new City()
                {
                    StateId = pulsatrixDb.State.FirstOrDefault(x => x.Name == temp).ID,
                    Name = RemoveSpaces(item.Name)
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportUniversityTypes()
        {
            Console.Write("Transporting university types => ");
            foreach (var item in stagingDb.VstupDb_UniversityType)
            {
                pulsatrixDb.UniversityType.Add(new UniversityType()
                {
                    Value = RemoveSpaces(item.Name)
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        /// <summary>
        /// Always check univs for 0 and empty strings!!!
        /// </summary>
        private void TransportUniversities()
        {
            Console.Write("Universities transportation => ");
            University u = null;
            int iterator = 0;
            foreach (var univ in stagingDb.VstupDb_University)
            {
                u = new University();
                u.YearId = univ.YearId;

                u.Name = RemoveSpaces(univ.Name);

                var abbr = stagingDb.OsvitaUa_University.FirstOrDefault(x => x.Name.ToLower() == u.Name.ToLower());
                if (abbr != null) u.Abbreviation = abbr.ShortName;
                else u.Abbreviation = "";

                var type = pulsatrixDb.UniversityType.FirstOrDefault(x => x.Value.ToLower() == univ.VstupDb_UniversityType.Name.ToLower());
                if (type != null) u.Type = type.ID;
                else u.Type = 0;

                var state = pulsatrixDb.State.FirstOrDefault(x => x.Name.ToLower() == univ.VstupDb_Region.Name.ToLower());
                if (state != null) u.State = state.ID;
                else u.State = 0;

                string address = univ.Address.ToLower();
                var city = pulsatrixDb.City.FirstOrDefault(x => address.Contains(x.Name.ToLower()));
                if (city != null)
                {
                    u.City = city.ID;
                    var region = pulsatrixDb.Region.FirstOrDefault(x => address.Contains(x.Name.ToLower()));
                    if (region != null) u.Region = region.ID;
                    else u.Region = 0;
                }
                else
                {
                    u.Address = RemoveSpaces(univ.Address);
                    u.City = 0;
                    u.Region = 0;
                }
                u.PostalIndex = univ.PostIndex;
                var univerOsvitaUa = stagingDb.OsvitaUa_University.FirstOrDefault(x => x.Name == u.Name);
                if (univerOsvitaUa != null)
                {
                    u.ShortInfo = univerOsvitaUa.DetailInfo;
                    u.Accreditation = univerOsvitaUa.AccreditationLevelName;
                    u.LogoPath = univerOsvitaUa.LogoPath;
                    u.Phone = univerOsvitaUa.Tel;
                    u.Email = univerOsvitaUa.Mail;
                    u.CostOfTraining = univerOsvitaUa.CostOfTraining;
                    u.FormOfStudying = univerOsvitaUa.FormOfStady;
                    u.FreeStudying = univerOsvitaUa.FreeStady;
                    u.Hostel = univerOsvitaUa.Hostel;
                    u.IsPrivate = univerOsvitaUa.Ownershipp != "державна";
                    u.MilitaryDepartment = univerOsvitaUa.MilitaryDepartment;
                    u.PhD = univerOsvitaUa.PostgraduateDoctoral;
                    u.Ratings = univerOsvitaUa.Ratings;
                    u.Website = univerOsvitaUa.Website;
                    u.PostGraduateEducation = univerOsvitaUa.PostgraduateEducation;
                    u.TrainingDepartment = univerOsvitaUa.TraininingDepartment;
                    u.PreparationCourses = univerOsvitaUa.PreparationCourses;
                    u.SourceLink += univerOsvitaUa.SourceSite += " \n";
                }

                var univerOsvitaComUa = stagingDb.OsvitaComUa_University.FirstOrDefault(x => x.Name == u.Name);
                if (univerOsvitaComUa != null)
                {
                    u.StudentsAmount = univerOsvitaComUa.CountOfStydent;
                    u.TeachersAmount = univerOsvitaComUa.CountOfTeacher;
                    u.CandidatesScAmount = univerOsvitaComUa.CountOfCandidatesOfSciences;
                    u.PhDAmount = univerOsvitaComUa.CountOfProfessorwsAndDoctors;
                    u.ApplyingInfo = univerOsvitaComUa.ForAbiturientsHTML;
                    u.UnivDetailedInfo = univerOsvitaComUa.DetailInfoAboutUniv;
                    u.SourceLink += univerOsvitaComUa.SourceHref + " \n";
                }
                pulsatrixDb.University.Add(u);

                if (iterator == 10)
                {
                    iterator = 0;
                    Console.Write(".");
                }
                iterator++;
                pulsatrixDb.SaveChanges();
            }
            // pulsatrixDb.SaveChanges();

            Console.Write(" OK\n");
        }
        private void TransportGallery()
        {
            Console.Write("Gallery transportation => ");
            Gallery gallery = null;
            foreach (var pic in stagingDb.OsvitaComUa_Images)
            {
                gallery = new Gallery();

                string univOsvitaName = pic.OsvitaComUa_University.Name;
                var univ = pulsatrixDb.University.FirstOrDefault(x => x.Name == univOsvitaName);
                if (univ != null) gallery.UniversityId = univ.ID;
                else gallery.UniversityId = 0;

                gallery.ImageName = pic.ImageName;

                pulsatrixDb.Gallery.Add(gallery);
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportFaculties()
        {
            Console.Write("Transporting faculties => ");
            int iterator = 0;
            foreach (var faculty in stagingDb.VstupDb_Faculty)
            {
                pulsatrixDb.Faculty.Add(new Faculty()
                {
                    ID = faculty.FacultyId,
                    UniversityID = pulsatrixDb.University.FirstOrDefault(x => x.Name == faculty.VstupDb_University.Name).ID,
                    Name = RemoveSpaces(faculty.Name)
                });
                if (iterator == 100)
                {
                    iterator = 0;
                    Console.Write(".");
                }
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportEQGroups()
        {
            Console.Write("EQ groups transportation => ");
            foreach (var item in stagingDb.VstupDb_EducationalQualificationsGroup)
            {
                pulsatrixDb.EQGroup.Add(new EQGroup()
                {
                    ID = item.EducationalQualificationsGroupId,
                    Name = RemoveSpaces(item.Name)
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportEQ()
        {
            Console.Write("EQs transportation => ");
            foreach (var item in stagingDb.VstupDb_EducationalQualifications)
            {
                pulsatrixDb.EQ.Add(new EQ()
                {
                    ID = item.EducationalQualificationsId,
                    Name = RemoveSpaces(item.Name),
                    EQGroupId = item.EducationalQualificationsGroupId,
                    YearId = item.YearId
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportBranches()
        {
            Console.Write("Branches transportation => ");
            foreach (var item in stagingDb.VstupDb_Branch)
            {
                pulsatrixDb.Branch.Add(new Branch()
                {
                    ID = item.BranchId,
                    EQGroupId = item.EducationalQualificationsGroupId,
                    Name = RemoveSpaces(item.Name),
                    YearId = item.YearId
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportDirectionTypes()
        {
            Console.Write("Direction types transportation => ");
            foreach (var item in stagingDb.VstupDb_DirectionType)
            {
                pulsatrixDb.DirectionType.Add(new DirectionType()
                {
                    ID = item.DirectionTypeId,
                    Name = RemoveSpaces(item.Name)
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportDirections() // CHANGE DIRECTION 0 ROW TYPE TO 0!!! in staging db
        {
            Console.Write("Directions transportation => ");
            foreach (var item in stagingDb.VstupDb_Direction)
            {
                Direction dir = new Direction();
                dir.ID = item.DirectionId;
                dir.Name = RemoveSpaces(item.Name);
                dir.BranchId = item.BranchId;
                dir.YearId = item.YearId;
                dir.Code = RemoveSpaces(item.Code);
                var name = item.DirectionTypeId;

                pulsatrixDb.Direction.Add(dir);
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportSubjects()
        {
            Console.Write("Subjects transportation => ");
            foreach (var item in stagingDb.VstupDb_Subject)
            {
                pulsatrixDb.Subject.Add(new Subject()
                {
                    ID = item.SubjectId,
                    Name = RemoveSpaces(item.Name),
                    YearId = item.YearId
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportEducationForms()
        {
            Console.Write("Edication forms transportation => ");
            foreach (var item in stagingDb.VstupDb_EducationForm)
            {
                pulsatrixDb.EducationForm.Add(new EducationForm()
                {
                    ID = item.EducationFormId,
                    Name = RemoveSpaces(item.Name)
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportSpecialities()
        {
            Console.Write("Specialities transportation => ");
            foreach (var item in stagingDb.VstupDb_Specialization)
            {
                pulsatrixDb.Speciality.Add(new Speciality()
                {
                    ID = item.SpecializationId,
                    Name = RemoveSpaces(item.Name),
                    BranchId = item.BranchId,
                    YearId = item.YearId,
                    Code = RemoveSpaces(item.Code)
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportSpecializations()
        {
            Console.Write("Specializations transportation => ");
            foreach (var item in stagingDb.VstupDb_ExpandedSpecialization)
            {
                pulsatrixDb.Specialization.Add(new Specialization()
                {
                    ID = item.ExpandedSpecializationId,
                    Name = RemoveSpaces(item.Name),
                    YearId = item.YearId,
                    DirectionId = item.DirectionId,
                    SpecialityId = item.SpecializationId,
                    BranchId = item.BranchId
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportAnnualSets()
        {
            Console.Write("Annual sets transportation => ");
            int counter = 0;
            int year = 0;
            foreach (var item in stagingDb.VstupDb_Speciality)
            {
                year = item.VstupDb_Year.Value;
                pulsatrixDb.AnnualSet.Add(new AnnualSet()
                {
                    ID = item.SpecialityId,
                    FacultyId = item.FacultyId,
                    DirectionId = item.DirectionId,
                    SpecialityId = item.SpecializationId,
                    EQId = item.EducationalQualificationsId,
                    YearId = item.YearId,
                    EducationForm = item.EducationFormId,
                    SpecializationId = item.ExpandedSpecializationId,
                    OfferedPlaces = item.License,
                    ScholarshipPlaces = item.Budjet,
                    VstupId = item.VstupId.Value
                });

                if (counter == 10)
                {
                    Console.Write(".");
                    counter = 0;
                }
                counter++;
                pulsatrixDb.SaveChanges();
            }
            Console.Write(" OK\n");
        }
        private void TransportAnnualSetSubjects()
        {
            Console.Write("AnnualSet & Subject connections transportation => ");
            foreach (var item in stagingDb.VstupDb_SpecialitySubject)
            {
                pulsatrixDb.AnnualSetSubject.Add(new AnnualSetSubject()
                {
                    ID = item.SpecialitySubjectId,
                    AnnualSetId = item.SpecialityId,
                    SubjectId = item.SubjectId,
                    IsCoreSubject = item.IsProfile
                });
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private void TransportProfessionGroups()
        {
            Console.Write("Profession groups transportation => ");

            foreach (var item in stagingDb.OsvitaComUa_GroupOfProfession)
            {
                pulsatrixDb.ProfessionGroup.Add(new ProfessionGroup()
                {
                    ID = item.Id,
                    Name = item.Name
                });
            }
            pulsatrixDb.SaveChanges();

            Console.Write(" OK\n");
        }
        private void TransportProfessions()
        {
            Console.Write("Professions transportation => ");

            foreach (var item in stagingDb.OsvitaComUa_Profession)
            {
                Profession prof = new Profession();
                prof.ID = item.Id;
                if (item.Name != null) prof.Name = item.Name;
                else prof.Name = "";
                prof.ProfessionGroupId = item.GroupOfProfessionId.Value;
                if (item.TheSpecificityOfTheProfession != null) prof.Info = item.TheSpecificityOfTheProfession;
                else prof.Info = "";
                if (item.PlaceOfJob != null) prof.PlaceOfJob = item.PlaceOfJob;
                else prof.PlaceOfJob = "";
                if (item.PersonalCharacteristics != null) prof.PersonalCharacteristics = item.PersonalCharacteristics;
                else prof.PersonalCharacteristics = "";
                if (item.Career != null) prof.Career = item.Career;
                else prof.Career = "";
                if (item.AllText != null) prof.Description = item.AllText;
                else prof.Description = "";
                if (item.SourceHref != null) prof.SourceLink = item.SourceHref;
                else prof.SourceLink = "";

                pulsatrixDb.Profession.Add(prof);
            }
            pulsatrixDb.SaveChanges();
            Console.Write(" OK\n");
        }
        private string RemoveSpaces(string input)
        {
            if (input != string.Empty && input != null)
            {
                int start = -1;
                char sign = ' ';
                while (sign == ' ')
                {
                    start++;
                    sign = input[start];
                }
                int end = input.Length;
                sign = ' ';
                while (sign == ' ')
                {
                    end--;
                    sign = input[end];
                }
                return input.Substring(start, end + 1 - start);
            }
            else return string.Empty;
        }
    }
    public class RealvnzDbTransporter
    {
        /// <summary>
        /// This code runs script RealvnzDbTransportation.sql. It transports all data from realvnzDb to PulsatrixStagingDb. 
        /// This code does NOT create PulsatrixStagingDb database! It it doesn't exist - exception will be caught.
        /// </summary>
        public Exception Run()
        {
            try
            {
                string script = "";
                string path = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName + @"\RealvnzDbTransportation.sql";
                if (File.Exists(path))
                {
                    using (FileStream fs = new FileStream(path, FileMode.Open))
                    {
                        using (StreamReader reader = new StreamReader(fs))
                        {
                            script = reader.ReadToEnd();
                            DbCommandExecuter executer = new DbCommandExecuter("PulsatrixStagingDbEntities");
                            Console.WriteLine(executer.ExecuteCommand(script).RecordsAffected + " records affected in common.");
                        }
                    }
                }
                else return new FileNotFoundException("Script RealvnzDbTransportation.sql doesn't exist in current directory: " + path);
                return null;
            }
            catch (Exception exc) { return exc; }
        }
    }
}
